package com.favccxx.search.infrastructure.util.jdbc;

import com.favccxx.search.domain.datasource.dataset.viewobject.DataSetDefinitionVo;
import com.favccxx.search.domain.datasource.dataset.viewobject.TableFieldVo;
import com.favccxx.search.infrastructure.constant.AppConstants;
import lombok.extern.slf4j.Slf4j;

import javax.security.auth.login.AppConfigurationEntry;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Slf4j
public class JdbcUtil {


    public static Connection init(String driver, String url, String username, String password) {
        Connection connection = null;
        try {
            Class.forName(driver).newInstance();
            connection = java.sql.DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            log.info(e.getMessage(), e);
        }

        return connection;
    }

    public List<String> getTables(Connection connection, String databaseName) {
        List<String> tables = new ArrayList<>();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                log.info("table: " + rs.getString("TABLE_NAME"));
                tables.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {

        }
        return tables;
    }


    /**
     * Get table definition by database name
     *
     * @param connection
     * @param schema     database name
     * @return
     */
    public static List<DataSetDefinitionVo> getTableDefinition(Connection connection, String schema) {
        List<DataSetDefinitionVo> dataList = new ArrayList<>();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(schema, null, null, new String[]{"TABLE"});

            while (rs.next()) {
                String databaseNme = rs.getString("TABLE_CAT");
                log.info("databaseName: " + databaseNme);
                if (schema.equalsIgnoreCase(databaseNme)) {
                    DataSetDefinitionVo dataSetDefinitionVo = new DataSetDefinitionVo();
                    String tableType = rs.getString("TABLE_TYPE");
                    String tableName = rs.getString("TABLE_NAME");
                    dataSetDefinitionVo.setTableName(tableName);
                    dataSetDefinitionVo.setTableType(tableType);
                    List<TableFieldVo> fieldList = new ArrayList<>();

                    String sql = "select * from " + databaseNme + "." + tableName;
                    PreparedStatement ps = connection.prepareStatement(sql);
                    ResultSet tableSet = ps.executeQuery();
                    ResultSetMetaData meta = tableSet.getMetaData();
                    int columeCount = meta.getColumnCount();
                    dataSetDefinitionVo.setFiledCount(columeCount);

                    for (int i = 1; i < columeCount + 1; i++) {
                        TableFieldVo tableField = new TableFieldVo();
                        tableField.setFieldName(meta.getColumnName(i));
                        tableField.setFieldType(typeChange(meta.getColumnTypeName(i)));
                        tableField.setFiledLength(meta.getPrecision(i));
                        fieldList.add(tableField);
                    }
                    dataSetDefinitionVo.setTableFields(fieldList);

                    dataList.add(dataSetDefinitionVo);
                }
            }
        } catch (Exception e) {
            log.error("JdbcUtil.getTableDefinition faield", e.getMessage());
        } finally {
            closeConnection(connection);
        }
        return dataList;
    }


    public static List<DataSetDefinitionVo> getTableDefinition(Connection connection, String schema, String[] tableNames) {
        List<DataSetDefinitionVo> dataList = new ArrayList<>();
        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet rs = dbMetaData.getTables(schema, null, null, new String[]{"TABLE"});

            while (rs.next()) {
                String databaseNme = rs.getString("TABLE_CAT");
                log.info("databaseName: " + databaseNme);
                if (schema.equalsIgnoreCase(databaseNme)) {
                    DataSetDefinitionVo dataSetDefinitionVo = new DataSetDefinitionVo();
                    String tableType = rs.getString("TABLE_TYPE");
                    String tableName = rs.getString("TABLE_NAME");
                    dataSetDefinitionVo.setTableName(tableName);
                    dataSetDefinitionVo.setTableType(tableType);
                    List<TableFieldVo> fieldList = new ArrayList<>();

                    List<String> tables = Arrays.asList(tableNames);
                    if (tables.contains(tableName)) {
                        String sql = "select * from " + databaseNme + "." + tableName;
                        PreparedStatement ps = connection.prepareStatement(sql);
                        ResultSet tableSet = ps.executeQuery();
                        ResultSetMetaData meta = tableSet.getMetaData();
                        int columeCount = meta.getColumnCount();
                        dataSetDefinitionVo.setFiledCount(columeCount);

                        for (int i = 1; i < columeCount + 1; i++) {
                            TableFieldVo tableField = new TableFieldVo();
                            tableField.setFieldName(meta.getColumnName(i));
                            tableField.setFieldType(typeChange(meta.getColumnTypeName(i)));
                            tableField.setFiledLength(meta.getPrecision(i));
                            fieldList.add(tableField);
                        }
                        dataSetDefinitionVo.setTableFields(fieldList);

                        dataList.add(dataSetDefinitionVo);
                    }
                }
            }
        } catch (Exception e) {
            log.error("JdbcUtil.getTableDefinition faield", e.getMessage());
        } finally {
            closeConnection(connection);
        }
        return dataList;
    }


    public void getTableFields(Connection connection, String databaseName, String tableName) {
        String sql = "Select * from " + databaseName + "." + tableName;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData meta = rs.getMetaData();
            int columeCount = meta.getColumnCount();


        } catch (Exception e) {

        }
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("close connection failure", e);
            }
        }
    }

    private static String typeChange(String type) {
        if (AppConstants.SQL_TYPE_CHAR.equalsIgnoreCase(type) || AppConstants.SQL_TYPE_VARCHAR.equalsIgnoreCase(type) || AppConstants.SQL_TYPE_NVARCHAR.equalsIgnoreCase(type)) {
            return "String";
        } else if ("INT".equalsIgnoreCase(type)) {
            return "Int";
        }
        return type;
    }

}
